package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Iterator;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.Organization;
import ro.gateway.aida.obj.Person;
import ro.gateway.aida.servlet.Constants;
import ro.gateway.aida.srv.ICFService;
import ro.gateway.aida.utils.LocalisedSortableList;

public class OrganizationDB extends DBPersistenceManager {
	private OrganizationDB(PersistenceToken token) {
		super(token);
	}

	public static OrganizationDB getManager(PersistenceToken token) {
		return new OrganizationDB(token);
	}

	/**
	 * Retrieves the name of the organization type, in the language of the
	 * Organization object given.
	 * 
	 * @param org -
	 *            Organization bean
	 * 
	 * @return - a string with the type name
	 */
	public String getTypeName(Organization org, String lang) {
		Connection con = getConnection();
		String ret = "";
		try {
			PreparedStatement ps = con.prepareStatement(typeName);
			ps.setString(1, lang);
			ps.setLong(2, org.getCatID());
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				ret = rs.getString(1);
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}
		return ret;
	}

	/**
	 * Retrieves the name of the Organization's contact person
	 * 
	 * @param org -
	 *            Organization bean
	 * 
	 * @return - the name of the person
	 * 
	 * @deprecated
	 */
	public String getPersonName(Organization org) {
		Connection con = getConnection();
		String ret = "";
		try {
			PreparedStatement ps = con.prepareStatement(personName);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				ret = rs.getString(1) + " " + rs.getString(2);
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		return ret;
	}

	/**
	 * Creates a formatted HTML list containing all the types in the database.
	 * <br>
	 * This will create a HTML dropdown list, but you must include from outside
	 * the < select .. > and < /select> tags.
	 * 
	 * @param selType -
	 *            the selected type, specify this if you want to specify the
	 *            default selected element.
	 * @param lang -
	 *            the language of the content
	 * 
	 * @return - a HTML formatted string
	 * 
	 * @deprecated
	 */
	public String createTypesList(long selType, String lang) {
		StringBuffer b = new StringBuffer();
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(typesList);
			ps.setString(1, lang);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String stat = ">";
				if (selType == rs.getLong(2))
					stat = " selected>";
				b.append("<option value=" + rs.getString(2) + stat
						+ rs.getString(1) + "</option>");
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}
		return b.toString();
	}

	/**
	 * Creates a formatted HTML list containing all the types in the database.
	 * <br>
	 * This will create a HTML dropdown list, but you must include from outside
	 * the < select .. > and < /select> tags.
	 * 
	 * @param org
	 *            Organization object to extract TypeID and language
	 * 
	 * @return - a HTML formatted string
	 * 
	 * @deprecated
	 */
	public String createTypesList(Organization org, String lang) {
		StringBuffer b = new StringBuffer();
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(typesList);
			ps.setString(1, lang);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String stat = ">";
				if (org.getCatID() == rs.getInt(2))
					stat = " selected>";
				b.append("<option value=" + rs.getString(2) + stat
						+ rs.getString(1) + "</option>");
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}
		return b.toString();
	}

	public String createPersonList(long persID) {
		StringBuffer b = new StringBuffer();
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(personsList);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String stat = ">";
				if (persID == rs.getInt(1))
					stat = " selected>";
				b
						.append("<option value=" + rs.getString(1) + stat
								+ rs.getString(2) + " " + rs.getString(3)
								+ "</option>");
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}
		return b.toString();
	}

	public String createCountryList(String countryISO3) {
		StringBuffer b = new StringBuffer();
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(countryList);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String stat = ">";
				if ((countryISO3 != null)
						&& countryISO3.equals(rs.getString(2)))
					stat = " selected>";
				b.append("<option value=" + rs.getString(2) + stat
						+ rs.getString(1) + "</option>");
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException sqlEx) {
			}
		}
		return b.toString();
	}

	public LocalisedSortableList getList(long user_id) throws SQLException {
		Connection con = null;
		LocalisedSortableList res = new LocalisedSortableList();
		try {
			con = getConnection();
			PreparedStatement ps1 = con.prepareStatement(ORG_OWNER + ORDER);
			ps1.setLong(1, user_id);
			ResultSet rs1 = ps1.executeQuery();
			Organization o = null;
			long prid = -1;
			String lang = null;
			while (rs1.next()) {
				if ((o == null) || (prid != rs1.getLong(15))) {
					o = new Organization();
					res.add(o);
					prid = rs1.getLong(15);
				}
				o.setCatID(rs1.getInt(1));
				o.setAcronym(rs1.getString(2));
				o.setCountryISO3(rs1.getString(3));
				o.setUserID(rs1.getLong(4));
				o.setAIDAOrg(rs1.getString(5));
				o.setCountryName(rs1.getString(6));
				o.setUpdate(rs1.getDate(7));
				o.setContactEmail(rs1.getString(8));
				o.setContactFax(rs1.getString(9));
				o.setContactName(rs1.getString(10));
				o.setContactPhone(rs1.getString(11));
				o.setContactURL(rs1.getString(12));
				o.setAddress1(rs1.getString(13));
				o.setAddress2(rs1.getString(14));
				o.setId(rs1.getLong(15));
				lang = rs1.getString(18);
				o.getName().setString(lang, rs1.getString(16));
				o.getDescription().setString(lang, rs1.getString(17));
				o.setTemporary(str2Bool(rs1.getString(19)));
				o.setInfoSource(rs1.getString(20));
				o.setViewLang(lang);
			}
			rs1.close();
			ps1.close();
		} finally {
			con.close();
		}
		res.setComparator(new Organization.DateComparator());
		return res;
	}

	public LocalisedSortableList getList() throws SQLException {
		Connection con = null;
		LocalisedSortableList res = new LocalisedSortableList();
		try {
			con = getConnection();
			PreparedStatement ps1 = con.prepareStatement(ORG_LIST + ORDER);
			ResultSet rs1 = ps1.executeQuery();
			Organization o = null;
			long prid = -1;
			String lang = null;
			while (rs1.next()) {
				if ((o == null) || (prid != rs1.getLong(15))) {
					o = new Organization();
					res.add(o);
					prid = rs1.getLong(15);
				}
				o.setCatID(rs1.getInt(1));
				o.setAcronym(rs1.getString(2));
				o.setCountryISO3(rs1.getString(3));
				o.setUserID(rs1.getLong(4));
				o.setAIDAOrg(rs1.getString(5));
				o.setCountryName(rs1.getString(6));
				o.setUpdate(rs1.getDate(7));
				o.setContactEmail(rs1.getString(8));
				o.setContactFax(rs1.getString(9));
				o.setContactName(rs1.getString(10));
				o.setContactPhone(rs1.getString(11));
				o.setContactURL(rs1.getString(12));
				o.setAddress1(rs1.getString(13));
				o.setAddress2(rs1.getString(14));
				o.setId(rs1.getLong(15));
				lang = rs1.getString(18);
				o.getName().setString(lang, rs1.getString(16));
				o.getDescription().setString(lang, rs1.getString(17));
				o.setTemporary(str2Bool(rs1.getString(19)));
				o.setInfoSource(rs1.getString(20));
				o.setViewLang(lang);
			}
			rs1.close();
			ps1.close();
		} finally {
			con.close();
		}
		return res;
	}

	public Organization getByID(long id) throws SQLException {
		Connection con = null;
		Organization res = null;
		OrgTypeDB otdb = OrgTypeDB.getManager(token);
		try {
			con = getConnection();
			PreparedStatement ps1 = con.prepareStatement(q1);
			ps1.setLong(1, id);
			ResultSet rs1 = ps1.executeQuery();
			if (rs1.next()) {
				res = new Organization();
				res.setId(id);
				res.setCatID(rs1.getInt(1));
				res.setAcronym(rs1.getString(2));
				res.setCountryISO3(rs1.getString(3));
				res.setUserID(rs1.getLong(4));
				res.setAIDAOrg(rs1.getString(5));
				res.setCountryName(rs1.getString(6));
				res.setUpdate(rs1.getDate(7));
				res.setContactEmail(rs1.getString(8));
				res.setContactFax(rs1.getString(9));
				res.setContactName(rs1.getString(10));
				res.setContactPhone(rs1.getString(11));
				res.setContactURL(rs1.getString(12));
				res.setAddress1(rs1.getString(13));
				res.setAddress2(rs1.getString(14));
				res.setTemporary(str2Bool(rs1.getString(15)));
				res.setInfoSource(rs1.getString(16));
				res.setContactTitle(rs1.getString(17));
			} else {
				rs1.close();
				ps1.close();
				con.close();
				return null;
			}
			rs1.close();
			ps1.close();
			con.close();
			con = getConnection();
			PreparedStatement ps2 = con.prepareStatement(q2);
			ps2.setLong(1, id);
			ResultSet rs2 = ps2.executeQuery();
			while (rs2.next()) {
				res.getName().setString(rs2.getString(1), rs2.getString(2));
				res.getDescription().setString(rs2.getString(1),
						rs2.getString(3));
			}
			rs2.close();
			ps2.close();
			con.close();
			//retrieve persons list
			ArrayList pers = new ArrayList();
			PersonDB pdb = PersonDB.getManager(token);
			con = getConnection();
			PreparedStatement ps3 = con.prepareStatement(getPersons);
			ps3.setLong(1, id);
			ResultSet rs3 = ps3.executeQuery();
			while (rs3.next()) {
				Person p = pdb.getByID(rs3.getLong(1));
				if (p != null)
					pers.add(p);
			}
			res.setPersons(pers);
			rs3.close();
			ps3.close();
			con.close();
		} catch (SQLException sqlEx) {
			try {
				if (con != null)
					con.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			throw sqlEx;
		} finally {
		}
		//retrieve type names
		res.setType(otdb.getById(res.getCatID()));
		return res;
	}

	public LocalisedSortableList getByLike(String like) throws SQLException {
		Connection con = null;
		LocalisedSortableList res = new LocalisedSortableList();
		try {
			con = getConnection();
			PreparedStatement ps1 = con.prepareStatement(ORG_LIKE + ORDER);
			//like = like.toUpperCase();
			ps1.setString(1, "%" + like + "%");
			ps1.setString(2, "%" + like + "%");
			ps1.setString(3, "%" + like + "%");
			ResultSet rs1 = ps1.executeQuery();
			Organization o = null;
			long prid = -1;
			String lang = null;
			while (rs1.next()) {
				if ((o == null) || (prid != rs1.getLong(15))) {
					o = new Organization();
					res.add(o);
					prid = rs1.getLong(15);
				}
				o.setCatID(rs1.getInt(1));
				o.setAcronym(rs1.getString(2));
				o.setCountryISO3(rs1.getString(3));
				o.setUserID(rs1.getLong(4));
				o.setAIDAOrg(rs1.getString(5));
				o.setCountryName(rs1.getString(6));
				o.setUpdate(rs1.getDate(7));
				o.setContactEmail(rs1.getString(8));
				o.setContactFax(rs1.getString(9));
				o.setContactName(rs1.getString(10));
				o.setContactPhone(rs1.getString(11));
				o.setContactURL(rs1.getString(12));
				o.setAddress1(rs1.getString(13));
				o.setAddress2(rs1.getString(14));
				o.setId(rs1.getLong(15));
				lang = rs1.getString(18);
				o.getName().setString(lang, rs1.getString(16));
				o.getDescription().setString(lang, rs1.getString(17));
				o.setTemporary(str2Bool(rs1.getString(19)));
				o.setInfoSource(rs1.getString(20));
			}
			rs1.close();
			ps1.close();
		} finally {
			con.close();
		}
		return res;
	}

	public LocalisedSortableList directorySearch(String name, int type,
			String keyword) throws SQLException {
		Connection con = null;
		LocalisedSortableList res = new LocalisedSortableList();
		try {
			con = getConnection();
			String QUERY = ORG_LIST;
		//	name = name.toUpperCase();

			//keyword = keyword.toUpperCase();
			if (name.length() != 0)
				QUERY += ORG_NAME;
			if (keyword.length() != 0)
				QUERY += ORG_KEY;
			if (type != -1)
				QUERY += ORG_TYPE2;
			
			QUERY += ORDER;
			
			PreparedStatement ps1=con.prepareStatement(QUERY);
			int qid = 1;
			String s1 = "%" + name + "%";
			
						
			if (name.length() != 0) 
					ps1.setString(qid++, s1);
					
			if (keyword.length() != 0) {
				ps1.setString(qid++, "%" + keyword + "%");
				ps1.setString(qid++, "%" + keyword + "%");
				ps1.setString(qid++, "%" + keyword + "%");
				ps1.setString(qid++, "%" + keyword + "%");
				ps1.setString(qid++, "%" + keyword + "%");
			}
			if (type != -1)
				ps1.setInt(qid++, type);
			
			ResultSet rs1 = ps1.executeQuery();
			
			Organization o = null;
			long prid = -1;
			String lang = null;
			while (rs1.next()) {
				if ((o == null) || (prid != rs1.getLong(15))) {
					o = new Organization();
					res.add(o);
					prid = rs1.getLong(15);
				}
				o.setCatID(rs1.getInt(1));
				o.setAcronym(rs1.getString(2));
				o.setCountryISO3(rs1.getString(3));
				o.setUserID(rs1.getLong(4));
				o.setAIDAOrg(rs1.getString(5));
				o.setCountryName(rs1.getString(6));
				o.setUpdate(rs1.getDate(7));
				o.setContactEmail(rs1.getString(8));
				o.setContactFax(rs1.getString(9));
				o.setContactName(rs1.getString(10));
				o.setContactPhone(rs1.getString(11));
				o.setContactURL(rs1.getString(12));
				o.setAddress1(rs1.getString(13));
				o.setAddress2(rs1.getString(14));
				o.setId(rs1.getLong(15));
				lang = rs1.getString(18);
				o.getName().setString(lang, rs1.getString(16));
				o.getDescription().setString(lang, rs1.getString(17));
				o.setTemporary(str2Bool(rs1.getString(19)));
				o.setInfoSource(rs1.getString(20));
			}
			rs1.close();
			ps1.close();
		} finally {
			con.close();
		}
		return res;
	}

	public LocalisedSortableList getOrgTemp() throws SQLException {
		Connection con = null;
		LocalisedSortableList res = new LocalisedSortableList();
		try {
			con = getConnection();
			PreparedStatement ps1 = con.prepareStatement(GET_ORG_TEMP + ORDER);
			ResultSet rs1 = ps1.executeQuery();
			Organization o = null;
			long prid = -1;
			String lang = null;
			while (rs1.next()) {
				if ((o == null) || (prid != rs1.getLong(15))) {
					o = new Organization();
					res.add(o);
					prid = rs1.getLong(15);
				}
				o.setCatID(rs1.getInt(1));
				o.setAcronym(rs1.getString(2));
				o.setCountryISO3(rs1.getString(3));
				o.setUserID(rs1.getLong(4));
				o.setAIDAOrg(rs1.getString(5));
				o.setCountryName(rs1.getString(6));
				o.setUpdate(rs1.getDate(7));
				o.setContactEmail(rs1.getString(8));
				o.setContactFax(rs1.getString(9));
				o.setContactName(rs1.getString(10));
				o.setContactPhone(rs1.getString(11));
				o.setContactURL(rs1.getString(12));
				o.setAddress1(rs1.getString(13));
				o.setAddress2(rs1.getString(14));
				o.setId(rs1.getLong(15));
				lang = rs1.getString(18);
				o.getName().setString(lang, rs1.getString(16));
				o.getDescription().setString(lang, rs1.getString(17));
				o.setTemporary(str2Bool(rs1.getString(19)));
				o.setInfoSource(rs1.getString(20));
			}
			rs1.close();
			ps1.close();
		} finally {
			con.close();
		}
		return res;
	}

	public LocalisedSortableList getByType(int type) throws SQLException {
		Connection con = null;
		LocalisedSortableList res = new LocalisedSortableList();
		try {
			con = getConnection();
			PreparedStatement ps1 = con.prepareStatement(ORG_TYPE + ORDER);
			ps1.setInt(1, type);
			ResultSet rs1 = ps1.executeQuery();
			Organization o = null;
			long prid = -1;
			String lang = null;
			while (rs1.next()) {
				if ((o == null) || (prid != rs1.getLong(15))) {
					o = new Organization();
					res.add(o);
					prid = rs1.getLong(15);
				}
				o.setCatID(rs1.getInt(1));
				o.setAcronym(rs1.getString(2));
				o.setCountryISO3(rs1.getString(3));
				o.setUserID(rs1.getLong(4));
				o.setAIDAOrg(rs1.getString(5));
				o.setCountryName(rs1.getString(6));
				o.setUpdate(rs1.getDate(7));
				o.setContactEmail(rs1.getString(8));
				o.setContactFax(rs1.getString(9));
				o.setContactName(rs1.getString(10));
				o.setContactPhone(rs1.getString(11));
				o.setContactURL(rs1.getString(12));
				o.setAddress1(rs1.getString(13));
				o.setAddress2(rs1.getString(14));
				o.setId(rs1.getLong(15));
				lang = rs1.getString(18);
				o.getName().setString(lang, rs1.getString(16));
				o.getDescription().setString(lang, rs1.getString(17));
				o.setTemporary(str2Bool(rs1.getString(19)));
				o.setInfoSource(rs1.getString(20));
			}
			rs1.close();
			ps1.close();
		} finally {
			con.close();
		}
		return res;
	}

	public LocalisedSortableList getByLikeAndType(String like, int type)
			throws SQLException {
		Connection con = null;
		LocalisedSortableList res = new LocalisedSortableList();
		//like = like.toUpperCase();
		try {
			con = getConnection();
			PreparedStatement ps1 = con.prepareStatement(ORG_LIKE_AND_TYPE
					+ ORDER);
			ps1.setInt(1, type);
			ps1.setString(2, "%" + like + "%");
			ps1.setString(3, "%" + like + "%");
			ps1.setString(4, "%" + like + "%");
			ResultSet rs1 = ps1.executeQuery();
			Organization o = null;
			long prid = -1;
			String lang = null;
			while (rs1.next()) {
				if ((o == null) || (prid != rs1.getLong(15))) {
					o = new Organization();
					res.add(o);
					prid = rs1.getLong(15);
				}
				o.setCatID(rs1.getInt(1));
				o.setAcronym(rs1.getString(2));
				o.setCountryISO3(rs1.getString(3));
				o.setUserID(rs1.getLong(4));
				o.setAIDAOrg(rs1.getString(5));
				o.setCountryName(rs1.getString(6));
				o.setUpdate(rs1.getDate(7));
				o.setContactEmail(rs1.getString(8));
				o.setContactFax(rs1.getString(9));
				o.setContactName(rs1.getString(10));
				o.setContactPhone(rs1.getString(11));
				o.setContactURL(rs1.getString(12));
				o.setAddress1(rs1.getString(13));
				o.setAddress2(rs1.getString(14));
				lang = rs1.getString(18);
				o.setId(rs1.getLong(15));
				o.getName().setString(lang, rs1.getString(16));
				o.getDescription().setString(lang, rs1.getString(17));
				o.setTemporary(str2Bool(rs1.getString(19)));
				o.setInfoSource(rs1.getString(20));
			}
			rs1.close();
			ps1.close();
		} finally {
			con.close();
		}
		return res;
	}

	public LocalisedSortableList reorder(String by, LocalisedSortableList l,
			String lang) {
		if ("name".equals(by))
			l.setComparator(new Organization.LangComparator(lang));
		return l;
	}

	public LocalisedSortableList getByCat(long catID, String lang)
			throws SQLException {
		Connection con = getConnection();
		Comparator c = new Organization.LangComparator(lang);
		LocalisedSortableList res = new LocalisedSortableList(c);
		try {
			PreparedStatement ps1 = con.prepareStatement(catList);
			ps1.setLong(1, catID);
			ResultSet rs1 = ps1.executeQuery();
			while (rs1.next())
				res.add(getByID(rs1.getLong(1)));
			rs1.close();
			ps1.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		return res;
	}

	public static long getLastId(ICFService cfService, String poolName)
			throws SQLException {
		long result = 0;
		Connection con = cfService.getConnection(poolName);
		try {
			PreparedStatement ps = con.prepareStatement(GET_MAX_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				result = rs.getLong(1);
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		return result;
	}

	public boolean isLangInserted(Organization org, String lang)
			throws SQLException {
		Connection con = getConnection();
		boolean ret;
		try {
			PreparedStatement ps = con.prepareStatement(verifylang);
			ps.setLong(1, org.getId());
			ps.setString(2, lang);
			ResultSet rs = ps.executeQuery();
			rs.next();
			ret = (rs.getInt(1) != 0);
			rs.close();
			ps.close();
			return ret;
		} catch (SQLException sqlex) {
			throw sqlex;
		} finally {
			con.close();
		}
	}

	public void update(Organization org, String lang) throws SQLException {
		Connection con = getConnection();
		PreparedStatement ps2 = null;
		try {
			if (isLangInserted(org, lang)) {
				//we make a language update, because the language items
				// already exist
				ps2 = con.prepareStatement(upd_orgs2);
				ps2.setString(5, lang);
			} else
				//we create a new lang record, because the language does not
				// exist in the database
				ps2 = con.prepareStatement(ins_orgs2);
			ps2.setString(1, lang);
			ps2.setString(2, org.getName().getString(lang));
			ps2.setString(3, org.getDescription().getString(lang));
			ps2.setLong(4, org.getId());
			ps2.executeUpdate();
			ps2.close();
			//now update the unlanguaged data:
			PreparedStatement ps1 = con.prepareStatement(upd_orgs1);
			ps1.setLong(1, org.getCatID());
			ps1.setString(2, org.getAcronym());
			ps1.setString(3, org.getCountryISO3());
			ps1.setString(4, org.getAIDAOrg());
			ps1.setDate(5, new Date(System.currentTimeMillis()));
			ps1.setString(6, org.getContactEmail());
			ps1.setString(7, org.getRawContactFax());
			ps1.setString(8, org.getContactName());
			ps1.setString(9, org.getRawContactPhone());
			ps1.setString(10, org.getContactURL());
			ps1.setString(11, org.getAddress1());
			ps1.setString(12, org.getAddress2());
			ps1.setLong(16, org.getId());
			ps1.setString(13, bool2Str(org.isTemporary()));
			ps1.setString(14, org.getInfoSource());
			ps1.setString(15, org.getContactTitle());
			ps1.executeUpdate();
			ps1.close();
			//clean person list
			PreparedStatement ps3 = con.prepareStatement(cleanPersons);
			ps3.setLong(1, org.getId());
			ps3.executeUpdate();
			ps3.close();
			//insert new person list
			Iterator ip = org.getPersons().iterator();
			while (ip.hasNext()) {
				Person pers = (Person) ip.next();
				ps3 = con.prepareStatement(insertPersons);
				ps3.setLong(2, org.getId());
				ps3.setLong(1, pers.getId());
				ps3.executeUpdate();
			}
			ps3.close();
		} finally {
			con.close();
		}
	}

	public long checkExistentAIDAId(String aidaorg) throws SQLException {
		Connection con = getConnection();
		long ret = 0;
		try {
			//add hierarchy deletion - delete everything that this referes.
			//wipe person link
			PreparedStatement ps3 = con
					.prepareStatement(CHECK_EXISTENT_AIDA_ORG);
			ps3.setString(1, aidaorg);
			ResultSet rs = ps3.executeQuery();
			if (rs.next())
				ret = rs.getLong(1);
			rs.close();
			ps3.close();
		} finally {
			con.close();
		}
		return ret;
	}

	public void linkOrgs(long tempId, long linkId) throws SQLException {
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(ORG_LINK_PROJECT);
			ps.setLong(1, linkId);
			ps.setLong(2, tempId);
			ps.executeUpdate();
			ps.close();
			ps = con.prepareStatement(ORG_LINK_PRJ_FILTER1);
			ps.setLong(1, linkId);
			ps.setLong(2, tempId);
			ps.executeUpdate();
			ps.close();
			ps = con.prepareStatement(ORG_LINK_PRJ_FILTER2);
			ps.setLong(1, linkId);
			ps.setLong(2, tempId);
			ps.executeUpdate();
			ps.close();
			ps = con.prepareStatement(ORG_LINK_PRJ_FILTER3);
			ps.setLong(1, linkId);
			ps.setLong(2, tempId);
			ps.executeUpdate();
			ps.close();
			ps = con.prepareStatement(ORG_LINK_PRJ_FILTER4);
			ps.setLong(1, linkId);
			ps.setLong(2, tempId);
			ps.executeUpdate();
			ps.close();
		} finally {
			con.close();
		}
	}

	public void delete(long orgid) throws SQLException {
		Connection con = getConnection();
		try {
			//add hierarchy deletion - delete everything that this refers.
			//wipe person link
			PreparedStatement ps3 = con.prepareStatement(cleanPersons);
			ps3.setLong(1, orgid);
			ps3.executeUpdate();
			ps3.close();
			PreparedStatement ps1 = con.prepareStatement(delete1);
			PreparedStatement ps2 = con.prepareStatement(delete2);
			ps1.setLong(1, orgid);
			ps2.setLong(1, orgid);
			ps2.executeUpdate();
			ps1.executeUpdate();
			ps1.close();
			ps2.close();
		} catch (SQLException ex) {
			throw ex;
		} finally {
			con.close();
		}
	}

	public long insert(Organization org, String lang) throws SQLException {
		Connection con = getConnection();
		long ret = -1;
		try {
			PreparedStatement ps1 = con.prepareStatement(ins_orgs1);
			PreparedStatement ps2 = con.prepareStatement(ins_orgs2);
			if (!org.hasId())
				org.setId(db_token.getIdgenerators().nextVal(
						Constants.IDGEN_ORGANIZATIONS));
			if (org.isTemporary())
				org.setAIDAOrg("TMP" + org.getId());
			ret = org.getId();
			ps1.setLong(4, org.getId());
			ps2.setLong(4, org.getId());
			ps1.setLong(1, org.getCatID());
			ps1.setString(2, org.getAcronym());
			ps1.setString(3, org.getCountryISO3());
			ps1.setLong(5, org.getUserID());
			ps1.setString(6, org.getAIDAOrg());
			ps1.setDate(7, new Date(System.currentTimeMillis()));
			ps1.setString(8, org.getContactEmail());
			ps1.setString(9, org.getRawContactFax());
			ps1.setString(10, org.getContactName());
			ps1.setString(11, org.getRawContactPhone());
			ps1.setString(12, org.getContactURL());
			ps1.setString(13, org.getAddress1());
			ps1.setString(14, org.getAddress2());
			ps1.setString(15, bool2Str(org.isTemporary()));
			ps1.setString(16, org.getInfoSource());
			ps1.setString(17, org.getContactTitle());
			//inserting in second table
			ps2.setString(1, lang);
			ps2.setString(2, org.getName().getString(lang));
			ps2.setString(3, org.getDescription().getString(lang));
			ps1.executeUpdate();
			ps2.executeUpdate();
			ps1.close();
			ps2.close();
			PreparedStatement ps3 = con.prepareStatement(cleanPersons);
			ps3.setLong(1, org.getId());
			ps3.executeUpdate();
			ps3.close();
			if (org.getPersons() == null)
				org.setPersons(new ArrayList());
			if (!org.getPersons().isEmpty()) {
				Iterator ip = org.getPersons().iterator();
				while (ip.hasNext()) {
					Person pers = (Person) ip.next();
					ps3 = con.prepareStatement(insertPersons);
					ps3.setLong(2, org.getId());
					ps3.setLong(1, pers.getId());
					ps3.executeUpdate();
				}
			}
			ps3.close();
		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
		} finally {
			con.close();
		}
		return ret;
	}

	public ArrayList getAllDataLatest(int count)
	  throws SQLException {
	ArrayList res = new ArrayList();
	Connection con = getConnection();

	try {
		int c = 0;
		PreparedStatement ps = con.prepareStatement(GET_UPDATED_IDS);
		ps.setInt(1, count);

		ResultSet rs = ps.executeQuery();

		while (rs.next()) {
		    long id=rs.getLong(1);
		    Organization o=getByID(id);
		    res.add(o);
		}
	} finally {
		con.close();
	}

	return res;
}
	private static final String GET_UPDATED_IDS="SELECT DISTINCT id,updated FROM orgs ORDER BY updated DESC,id LIMIT ?";
	

	//query strings
	private static final String ORG_LINK_PROJECT = "UPDATE orgs_inv SET org_id=? WHERE org_id=?";

	private static final String ORG_LINK_PRJ_FILTER1 = "UPDATE prj_filters SET forg=? WHERE forg=?";

	private static final String ORG_LINK_PRJ_FILTER2 = "UPDATE prj_filters SET borg=? WHERE borg=?";

	private static final String ORG_LINK_PRJ_FILTER3 = "UPDATE prj_filters SET corg=? WHERE corg=?";

	private static final String ORG_LINK_PRJ_FILTER4 = "UPDATE prj_filters SET dorg=? WHERE dorg=?";

	private static final String CHECK_EXISTENT_AIDA_ORG = "SELECT id FROM orgs WHERE UPPER(aidaorg)=UPPER(?)";

	private static final String ORG_NAME = " AND d.name ILIKE(?) ";

	private static final String ORG_TYPE2 = " AND o.cat_id=? ";

	private static final String ORG_KEY = " AND ( o.aidaorg ILIKE(?) OR o.acronym ILIKE(?) OR "
			+ " o.address1 ILIKE(?) OR o.address2 ILIKE(?) OR d.description ILIKE(?) )";

	private static final String GET_MAX_ID = "select max(id) from orgs";

	private static final String insertPersons = "insert into org_pers (pers_id,org_id) values (?,?)";

	private static final String cleanPersons = "delete from org_pers where org_id=?";

	private static final String getPersons = "select pers_id from org_pers where org_id=?";

	private static final String multiget = "select lang,name,description from orgs_desc where id=?";

	private static final String allList = "select id from orgs order by id";

	private static final String allListUser = "select id from orgs where user_id=? ORDER BY updated";

	private static final String likeList = "select distinct o.id from orgs o, orgs_desc d where o.id=d.id and "
			+ " (UPPER(d.name) ilike(?) or UPPER(o.acronym) ilike(?) ) ";

	private static final String likeAndTypeList = "select distinct o.id from orgs o, orgs_desc d where o.id=d.id and o.cat_id=? and"
			+ " (d.name ILIKE(?) or o.acronym ILIKE(?) ) ";

	private static final String typeList = "SELECT distinct id FROM orgs WHERE cat_id=?";

	private static final String catList = "select id from orgs where cat_id=?";

	private static final String personsList = "select id,first_names,last_name from persons order by first_names";

	private static final String getType = "select lang,name from org_cats where id=?";

	private static final String typeName = "select name from org_cats where lang=? and id=?";

	private static final String personName = "select first_names,last_name from persons where id=?";

	private static final String typesList = "select name,id from org_cats where lang=? and id > 0 order by name";

	private static final String countryList = "select name,iso3 from countries_iso3 order by name";

	private static final String delete1 = "delete from orgs where id=?";

	private static final String delete2 = "delete from orgs_desc where id=?";

	private static final String verifylang = "select count(*) from orgs_desc where id=? and lang=?";

	private static final String q1 = "select o.cat_id,o.acronym,o.country_iso3,o.user_id,o.aidaorg,c.name,o.updated,"
			+ "o.email,o.fax,o.cname,o.phone,o.url,o.address1,o.address2, o.orgtemp, o.infosource,o.ctitle "
			+ "from orgs o LEFT OUTER JOIN countries_iso3 c ON (o.country_iso3=c.iso3) where id=? order by o.updated";

	private static final String q2 = "select lang,name,description from orgs_desc where id=?";

	private static final String ins_orgs1 = "insert into orgs (cat_id,acronym,country_iso3,id,user_id,aidaorg,updated,email,fax,cname,phone,url,address1,address2,orgtemp,infosource,ctitle)"
			+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private static final String ins_orgs2 = "insert into orgs_desc (lang,name,description,id)"
			+ " values (?,?,?,?)";

	private static final String upd_orgs1 = "update orgs set cat_id=?,acronym=?,country_iso3=?,aidaorg=?,updated=?,email=?,fax=?,cname=?,phone=?,"
			+ "url=?,address1=?,address2=?,orgtemp=?,infosource=?, ctitle=? where id=?";

	private static final String upd_orgs2 = "update orgs_desc set lang=?,name=?,description=? where id=? and lang=?";

	private static final String ORDER = " order by d.id";

	private static final String ORG_LIST = "select o.cat_id,o.acronym,o.country_iso3,o.user_id,o.aidaorg,c.name,o.updated,"
			+ "o.email,o.fax,o.cname,o.phone,o.url,o.address1,o.address2,o.id,d.name,d.description,d.lang, o.orgtemp, o.infosource "
			+ "from orgs o  LEFT OUTER JOIN countries_iso3 c ON (o.country_iso3=c.iso3) ,orgs_desc d where d.id=o.id";

	private static final String ORG_LIKE = ORG_LIST
			+ " and d.name ILIKE(?) or o.acronym ILIKE(?) or o.aidaorg ILIKE(?) ";

	private static final String ORG_TYPE = ORG_LIST + " and o.cat_id=?";

	private static final String ORG_OWNER = ORG_LIST + " and o.user_id=?";

	private static final String ORG_LIKE_AND_TYPE = ORG_LIST
			+ " and o.cat_id=?"
			+ " and (d.name ILIKE(?) or o.acronym ILIKE(?) or o.aidaorg ILIKE(?)) ";

	private static final String GET_ORG_TEMP = ORG_LIST + " AND o.orgtemp='Y'";
}